package com.cal.utms.dao.dealing;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;

import com.cal.utms.dto.dealing.FdDealDto;
import com.cal.utms.dto.dealing.InsIssueReceiveDto;
import com.cal.utms.dto.dealing.PurchaseDealDto;
import com.cal.utms.dto.dealing.RepoDealDto;
import com.cal.utms.dto.dealing.RevRepoDealDto;
import com.cal.utms.dto.dealing.SaleDealDto;
import com.cal.utms.dto.dealing.ShortMaturityDto;
import com.cal.utms.entity.Deal;
import com.cal.utms.entity.DealInstrumentMap;
import com.cal.utms.entity.Instrument;
import com.cal.utms.entity.InstrumentCouponDate;
import com.cal.utms.entity.InstrumentFloatingRate;
import com.cal.utms.entity.InstrumentTransaction;
import com.cal.utms.entity.MoneyTransaction;
import com.cal.utms.entity.SaledealMapping;
import com.cal.utms.logAudit.AuditLogInterceptor;
import com.cal.utms.util.HibernateUtil;

public class DealDao {

	@SuppressWarnings("unchecked")
	public Object addPurchase(Deal deal, Instrument instrument, PurchaseDealDto dealDto) {
		Object obj;
		AuditLogInterceptor interceptor = new AuditLogInterceptor();
		Session session = HibernateUtil.getSession().openSession(interceptor);
		interceptor.setSession(session);
		Transaction tx = null;
		System.out.println("Saving new deal");
		try {
			tx = session.beginTransaction();
			obj = session.save(deal);
			if (deal.getDeal() != null) {
				session.saveOrUpdate(deal.getDeal());
			}
			if (instrument.getInstrumentType().getListed() == 0) {
				session.save(instrument);
			} else {
				session.update(instrument);
			}

			Query query = session.createQuery("select c from InstrumentCouponDate c where c.instrument.instrumentCode = :code");
			query.setParameter("code", instrument.getInstrumentCode());
			List<InstrumentCouponDate> icdOldList = query.list();
			if (icdOldList.size() > 0) {
				for (InstrumentCouponDate icd : icdOldList)
					session.delete(icd);
			}

			if (instrument.getInstrumentCouponDates() != null && instrument.getInstrumentCouponDates().size() > 0)
				for (InstrumentCouponDate icd : instrument.getInstrumentCouponDates()) {
					session.save(icd);
				}

			if (instrument.getInstrumentFloatingRates() != null && instrument.getInstrumentFloatingRates().size() > 0)
				for (InstrumentFloatingRate ifr : instrument.getInstrumentFloatingRates()) {
					session.save(ifr);
				}

			for (DealInstrumentMap dim : deal.getDealInstrumentMaps()) {
				session.save(dim);
				for (InstrumentTransaction insTran : dim.getInstrumentTransactions()) {
					session.save(insTran);
				}
			}
			tx.commit();
			System.out.println("Deal Added: " + deal.getDealCode());
			dealDto.setSuccess(true);
		} catch (Exception ex) {
			tx.rollback();
			obj = new Object();
			dealDto.setStatus("DB Error!");
			dealDto.setSuccess(false);
			System.out.println("Exception in save Deal " + deal.getDealCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
		return obj;
	}

	public void updatePurchase(Deal deal, Instrument instrument, PurchaseDealDto dealDto) {
		System.out.println("Updating the Purchase Deal");
		AuditLogInterceptor interceptor = new AuditLogInterceptor();
		Session session = HibernateUtil.getSession().openSession(interceptor);
		interceptor.setSession(session);
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.update(deal);
			session.update(instrument);
			if (instrument.getInstrumentFloatingRates() != null && instrument.getInstrumentFloatingRates().size() > 0) {
				for (InstrumentFloatingRate ifr : instrument.getInstrumentFloatingRates()) {
					session.update(ifr);
				}
			}

			Query query = session.createQuery("select c from InstrumentCouponDate c where c.instrument.instrumentCode = :code");
			query.setParameter("code", instrument.getInstrumentCode());
			@SuppressWarnings("unchecked")
			List<InstrumentCouponDate> icdOldList = query.list();
			if (icdOldList.size() > 0) {
				for (InstrumentCouponDate icd : icdOldList)
					session.delete(icd);
			}

			if (instrument.getInstrumentCouponDates() != null && instrument.getInstrumentCouponDates().size() > 0)
				for (InstrumentCouponDate icd : instrument.getInstrumentCouponDates()) {
					session.save(icd);
				}

			tx.commit();
			System.out.println("Purchase Deal updated");
			dealDto.setSuccess(true);
		} catch (Exception ex) {
			tx.rollback();
			dealDto.setStatus("DB Error!");
			dealDto.setSuccess(false);
			System.out.println("Exception in Updating the Deal. ID: " + deal.getDealCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
	}

	public Object addSale(Deal deal, Instrument instrument, SaleDealDto saleDto, SaledealMapping mapping) {
		Object obj;
		AuditLogInterceptor interceptor = new AuditLogInterceptor();
		Session session = HibernateUtil.getSession().openSession(interceptor);
		interceptor.setSession(session);
		Transaction tx = null;
		System.out.println("Saving new deal");
		try {
			tx = session.beginTransaction();
			obj = session.save(deal);
			session.update(instrument);

			for (DealInstrumentMap dim : deal.getDealInstrumentMaps()) {
				session.save(dim);
				for (InstrumentTransaction insTran : dim.getInstrumentTransactions()) {
					session.save(insTran);
				}
			}
			if (mapping != null) {
				session.save(mapping);
			}
			tx.commit();
			System.out.println("Deal Added: " + deal.getDealCode());
			saleDto.setSuccess(true);
		} catch (Exception ex) {
			tx.rollback();
			obj = new Object();
			saleDto.setStatus("DB Error!");
			saleDto.setSuccess(false);
			System.out.println("Exception in save Deal " + deal.getDealCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
		return obj;
	}

	public Object addRepo(Deal deal, RepoDealDto repoDealDto) {
		Object obj;
		AuditLogInterceptor interceptor = new AuditLogInterceptor();
		Session session = HibernateUtil.getSession().openSession(interceptor);
		interceptor.setSession(session);
		Transaction tx = null;
		System.out.println("Saving new Repo");
		try {
			tx = session.beginTransaction();
			obj = session.save(deal);
			tx.commit();
			System.out.println("Deal Added: " + deal.getDealCode());
			repoDealDto.setSuccess(true);
		} catch (Exception ex) {
			tx.rollback();
			obj = new Object();
			repoDealDto.setStatus("DB Error!");
			repoDealDto.setSuccess(false);
			System.out.println("Exception in save Deal " + deal.getDealCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
		return obj;
	}

	public Object addFd(Deal deal, FdDealDto fdDealDto) {
		Object obj;
		AuditLogInterceptor interceptor = new AuditLogInterceptor();
		Session session = HibernateUtil.getSession().openSession(interceptor);
		interceptor.setSession(session);
		Transaction tx = null;
		System.out.println("Saving new FD");
		try {
			tx = session.beginTransaction();
			obj = session.save(deal);
			tx.commit();
			System.out.println("Deal Added: " + deal.getDealCode());
			fdDealDto.setSuccess(true);
		} catch (Exception ex) {
			tx.rollback();
			obj = new Object();
			fdDealDto.setStatus("DB Error!");
			fdDealDto.setSuccess(false);
			System.out.println("Exception in save Deal " + deal.getDealCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
		return obj;
	}

	public Object addRevRepo(Deal deal, RevRepoDealDto revRepoDealDto) {
		Object obj;
		AuditLogInterceptor interceptor = new AuditLogInterceptor();
		Session session = HibernateUtil.getSession().openSession(interceptor);
		interceptor.setSession(session);
		Transaction tx = null;
		System.out.println("Saving new Repo");
		try {
			tx = session.beginTransaction();
			obj = session.save(deal);
			tx.commit();
			System.out.println("Deal Added: " + deal.getDealCode());
			revRepoDealDto.setSuccess(true);
		} catch (Exception ex) {
			tx.rollback();
			obj = new Object();
			revRepoDealDto.setStatus("DB Error!");
			revRepoDealDto.setSuccess(false);
			System.out.println("Exception in save Deal " + deal.getDealCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
		return obj;
	}

	public void update(Deal deal) {
		System.out.println("Updating the deal");
		AuditLogInterceptor interceptor = new AuditLogInterceptor();
		Session session = HibernateUtil.getSession().openSession(interceptor);
		interceptor.setSession(session);
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.update(deal);
			tx.commit();
			System.out.println("Deal updated");
		} catch (Exception ex) {
			tx.rollback();
			System.out.println("Exception in Updating the Deal. ID: " + deal.getDealCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
	}

	public void delete(Deal deal) {
		System.out.println("Deleting the deal");
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			for (DealInstrumentMap dim : deal.getDealInstrumentMaps()) {
				for (InstrumentTransaction insTra : dim.getInstrumentTransactions()) {
					session.delete(insTra);
				}
				session.delete(dim);
			}
			session.delete(deal);
			tx.commit();
			System.out.println("Deal deleted");
		} catch (Exception ex) {
			tx.rollback();
			System.out.println("Exception in deleting the Deal. ID: " + deal.getDealCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
	}

	/*
	 * public void deleteSale(Deal deal){
	 * System.out.println("Deleting the deal"); Session session =
	 * HibernateUtil.getSession().openSession(); Transaction tx = null; try { tx
	 * = session.beginTransaction(); for(DealInstrumentMap dim:
	 * deal.getDealInstrumentMaps()){ for(InstrumentTransaction insTra:
	 * dim.getInstrumentTransactions()){ session.delete(insTra); }
	 * session.delete(dim); } tx.commit();
	 * System.out.println("Deal Ins Maps deleted"); } catch (Exception ex) {
	 * tx.rollback(); System.out.println("Exception in deleting the Deal. ID: "
	 * + deal.getDealCode() + " : " + ex.getMessage()); ex.printStackTrace(); }
	 * finally { session.close(); } }
	 */

	public void deleteInsTrans(Deal deal) {
		System.out.println("Deleting the deal");
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			for (DealInstrumentMap dim : deal.getDealInstrumentMaps()) {
				for (InstrumentTransaction insTra : dim.getInstrumentTransactions()) {
					session.delete(insTra);
				}
				session.delete(dim);
			}
			tx.commit();
			System.out.println("Deal Ins Maps deleted");
		} catch (Exception ex) {
			tx.rollback();
			System.out.println("Exception in deleting the Deal. ID: " + deal.getDealCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
	}

	public void confirm(Deal deal, List<MoneyTransaction> moneyTransactions) {
		System.out.println("Confirming the deal");
		AuditLogInterceptor interceptor = new AuditLogInterceptor();
		Session session = HibernateUtil.getSession().openSession(interceptor);
		interceptor.setSession(session);
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.update(deal);
			if (deal.getDealInstrumentMaps().size() > 0 && deal.getDealInstrumentMaps().get(0) != null && deal.getDealInstrumentMaps().get(0).getInstrument() != null) {
				session.update(deal.getDealInstrumentMaps().get(0).getInstrument());
			}
			if (deal.getDeal() != null)
				session.update(deal.getDeal());
			for (MoneyTransaction moneyTransaction : moneyTransactions) {
				session.save(moneyTransaction);
			}
			tx.commit();
			System.out.println("Deal updated");
		} catch (Exception ex) {
			tx.rollback();
			System.out.println("Exception in Updating the Deal. ID: " + deal.getDealCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
		}
	}

	public void confirmSale(Deal deal, Instrument instrument, List<MoneyTransaction> moneyTransactions) {
		System.out.println("Confirming the deal");
		AuditLogInterceptor interceptor = new AuditLogInterceptor();
		Session session = HibernateUtil.getSession().openSession(interceptor);
		Session session2 = HibernateUtil.getSession().openSession();
		interceptor.setSession(session);
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.update(deal);
			session.update(instrument);
			if (deal.getDealInstrumentMaps().size() > 0 && deal.getDealInstrumentMaps().get(0) != null && deal.getDealInstrumentMaps().get(0).getInstrument() != null) {
				session2.update(deal.getDealInstrumentMaps().get(0).getInstrument());
			}
			if (deal.getDeal() != null)
				session.update(deal.getDeal());
			for (MoneyTransaction moneyTransaction : moneyTransactions) {
				session.save(moneyTransaction);
			}
			tx.commit();
			System.out.println("Deal updated");
		} catch (Exception ex) {
			tx.rollback();
			System.out.println("Exception in Updating the Deal. ID: " + deal.getDealCode() + " : " + ex.getMessage());
			ex.printStackTrace();
		} finally {
			session.close();
			session2.close();
		}
	}

	public Deal getById(Integer id) {
		Session session = HibernateUtil.getSession().openSession();
		Deal deal = null;
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			deal = (Deal) session.get(Deal.class, id);
			tx.commit();
		} catch (Exception e) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getByID Deal " + e.getMessage());
			e.printStackTrace();
		} finally {
			session.close();
		}
		return deal;
	}

	public Deal getByCode(String code) {
		Session session = HibernateUtil.getSession().openSession();
		Deal deal = null;
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery("select c from Deal c where c.dealCode = :code");
			query.setParameter("code", code);
			@SuppressWarnings("rawtypes")
			List res = query.list();
			if (res.size() > 0) {
				deal = (Deal) res.get(0);
			} else {
				deal = null;
			}
			tx.commit();
		} catch (Exception e) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getByID Deal " + e.getMessage());
			e.printStackTrace();
		} finally {
			session.close();
		}
		return deal;
	}

	@SuppressWarnings("unchecked")
	public List<Deal> getByDealType(String dealType) {
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		List<Deal> res = null;
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery("select d from Deal d where d.dealType.code = :dealType");
			query.setParameter("dealType", dealType);
			res = query.list();
			tx.commit();
		} catch (Exception e) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getByDealType Deal " + e.getMessage());
			e.printStackTrace();
		} finally {
			session.close();
		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public List<Deal> getConfByDealType(String dealType) {
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		List<Deal> res = null;
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery("select d from Deal d where d.dealType.code = :dealType and d.status='Confirmed'");
			query.setParameter("dealType", dealType);
			res = query.list();
			tx.commit();
		} catch (Exception e) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getByDealType Deal " + e.getMessage());
			e.printStackTrace();
		} finally {
			session.close();
		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public List<Deal> getRollOverListByDealType(String dealType) {
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		List<Deal> res = null;
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery("select d from Deal d where d.dealType.code = :dealType and d.status='Confirmed' and d.rolloverShortmaturity=null");
			query.setParameter("dealType", dealType);
			res = query.list();
			tx.commit();
		} catch (Exception e) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getByDealType Deal " + e.getMessage());
			e.printStackTrace();
		} finally {
			session.close();
		}
		return res;
	}

	/*
	 * public List<Deal> getRollOverListByDealType(String dealType) { Session
	 * session = HibernateUtil.getSession().openSession(); //Criteria cr =
	 * session.createCriteria(Deal.class).createAlias("dealType", "type");
	 * Criteria cr = session.createCriteria(Deal.class); cr.createAlias("fund",
	 * "fnd"); cr.createAlias("issuer", "issur"); cr.createAlias("dealType",
	 * "dealTyp"); ProjectionList projectionList = Projections.projectionList();
	 * projectionList.add(Projections.property("id"), "id");
	 * projectionList.add(Projections.property("dealCode"), "dealCode");
	 * projectionList.add(Projections.property("fnd.fundCode"));
	 * projectionList.add(Projections.property("issur.code"));
	 * projectionList.add(Projections.property("dealValue"), "dealValue");
	 * projectionList.add(Projections.property("maturityDate"), "maturityDate");
	 * projectionList.add(Projections.property("maturityValue"),
	 * "maturityValue");
	 * projectionList.add(Projections.property("dealTyp.code"));
	 * cr.setProjection(projectionList); cr.add(Restrictions.eq("status",
	 * "Confirmed")); //cr.add(Restrictions.eq("this.rolloverShortmaturity",
	 * null)); cr.add(Restrictions.eq("dealTyp.code", dealType));
	 * //setResultTransformer( new
	 * AliasToBeanResultTransformer(StudentDTO.class)
	 * cr.setResultTransformer(new AliasToBeanResultTransformer(Fund.class));
	 * cr.setResultTransformer(new
	 * AliasToBeanResultTransformer(DealType.class));
	 * cr.setResultTransformer(new AliasToBeanResultTransformer(Issuer.class));
	 * cr.setResultTransformer(Transformers.aliasToBean(Deal.class));
	 * 
	 * @SuppressWarnings("unchecked") List<Deal> list = cr.list();
	 * session.close(); return list; }
	 */
	// Query query =
	// session.createQuery("select c from CustodianContactPerson c where c.deal.id = :dealId");

	@SuppressWarnings("unchecked")
	public List<Deal> getDealCodes() {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Deal.class);
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("dealCode"), "dealCode");
		projectionList.add(Projections.property("dealType"), "dealType");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		cr.setProjection(projectionList);
		cr.setResultTransformer(Transformers.aliasToBean(Deal.class));
		List<Deal> list = cr.list();
		session.close();
		return list;
	}

	public List<Deal> queryDeals(String typeCode) {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Deal.class).createAlias("dealType", "type");
		// cr.createAlias("dealType", "type");
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("dealCode"), "dealCode");
		projectionList.add(Projections.property("dealType"), "dealType");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		projectionList.add(Projections.property("name"), "name");
		cr.setProjection(projectionList);
		cr.add(Restrictions.eq("type.code", typeCode));
		cr.add(Restrictions.or(Restrictions.eq("this.status", "Initial"), Restrictions.eq("this.status", "Confirmed")));
		cr.setResultTransformer(Transformers.aliasToBean(Deal.class));
		@SuppressWarnings("unchecked")
		List<Deal> list = cr.list();
		session.close();
		return list;
	}

	public List<Deal> queryDeals(String typeCode, String param) {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Deal.class).createAlias("dealType", "type");
		// cr.createAlias("dealType", "type");
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("dealCode"), "dealCode");
		projectionList.add(Projections.property("dealType"), "dealType");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		projectionList.add(Projections.property("name"), "name");
		cr.setProjection(projectionList);
		cr.add(Restrictions.eq("type.code", typeCode));
		cr.add(Restrictions.or(Restrictions.eq("this.status", "Initial"), Restrictions.eq("this.status", "Confirmed")));
		cr.add(Restrictions.or(Restrictions.ilike("this.dealCode", param + "%"), Restrictions.ilike("this.name", "%" + param + "%")));
		cr.setResultTransformer(Transformers.aliasToBean(Deal.class));
		@SuppressWarnings("unchecked")
		List<Deal> list = cr.list();
		session.close();
		return list;
	}

	public List<Deal> queryInitDeals(String typeCode) {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Deal.class).createAlias("dealType", "type");
		// cr.createAlias("dealType", "type");
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("dealCode"), "dealCode");
		projectionList.add(Projections.property("dealType"), "dealType");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		projectionList.add(Projections.property("name"), "name");
		cr.setProjection(projectionList);
		cr.add(Restrictions.eq("type.code", typeCode));
		cr.add(Restrictions.eq("this.status", "Initial"));
		cr.setResultTransformer(Transformers.aliasToBean(Deal.class));
		@SuppressWarnings("unchecked")
		List<Deal> list = cr.list();
		session.close();
		return list;
	}

	public List<Deal> queryInitDeals(String typeCode, String param) {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Deal.class).createAlias("dealType", "type");
		// cr.createAlias("dealType", "type");
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("dealCode"), "dealCode");
		projectionList.add(Projections.property("dealType"), "dealType");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		projectionList.add(Projections.property("name"), "name");
		cr.setProjection(projectionList);
		cr.add(Restrictions.eq("type.code", typeCode));
		cr.add(Restrictions.eq("this.status", "Initial"));
		cr.add(Restrictions.or(Restrictions.ilike("this.dealCode", param + "%"), Restrictions.ilike("this.name", "%" + param + "%")));
		cr.setResultTransformer(Transformers.aliasToBean(Deal.class));
		@SuppressWarnings("unchecked")
		List<Deal> list = cr.list();
		session.close();
		return list;
	}

	public List<Deal> queryConfirmedDeals() {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Deal.class);
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("dealCode"), "dealCode");
		projectionList.add(Projections.property("dealType"), "dealType");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		projectionList.add(Projections.property("maturityDate"), "maturityDate");
		projectionList.add(Projections.property("dealValue"), "dealValue");
		projectionList.add(Projections.property("maturityValue"), "maturityValue");
		projectionList.add(Projections.property("withholdingCertificate"), "withholdingCertificate");
		cr.setProjection(projectionList);
		cr.add(Restrictions.eq("this.status", "Confirmed"));
		cr.setResultTransformer(Transformers.aliasToBean(Deal.class));
		@SuppressWarnings("unchecked")
		List<Deal> list = cr.list();
		session.close();
		return list;
	}

	public List<Deal> filterInitialDeals() {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Deal.class);
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("dealCode"), "dealCode");
		projectionList.add(Projections.property("dealType"), "dealType");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		projectionList.add(Projections.property("maturityDate"), "maturityDate");
		projectionList.add(Projections.property("dealValue"), "dealValue");
		projectionList.add(Projections.property("withholdingCertificate"), "withholdingCertificate");
		cr.setProjection(projectionList);
		cr.add(Restrictions.not(Restrictions.eq("this.status", "Initial")));
		cr.setResultTransformer(Transformers.aliasToBean(Deal.class));
		@SuppressWarnings("unchecked")
		List<Deal> list = cr.list();
		session.close();
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Deal> getNames(String query) {
		Session session = HibernateUtil.getSession().openSession();
		Criteria cr = session.createCriteria(Deal.class);
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("id"), "id");
		projectionList.add(Projections.property("dealCode"), "dealCode");
		projectionList.add(Projections.property("dealType"), "dealType");
		projectionList.add(Projections.property("issuer"), "issuer");
		projectionList.add(Projections.property("status"), "status");
		cr.setProjection(projectionList);
		cr.setResultTransformer(Transformers.aliasToBean(Deal.class));
		List<Deal> list = cr.list();
		session.close();
		List<Deal> res = new ArrayList<Deal>();
		for (Deal deal : list) {
			if (deal.getStatus().equals("Free") && deal.getDealCode().toUpperCase().startsWith(query.toUpperCase())) {
				res.add(deal);
			}
		}
		return res;
	}

	public void updateDims(List<DealInstrumentMap> dimList, InsIssueReceiveDto insIssueReceiveDto) {
		System.out.println("Updating the DIMs");
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			for (DealInstrumentMap dim : dimList) {
				session.saveOrUpdate(dim);
				session.saveOrUpdate(dim.getInstrument());
				for (InstrumentTransaction insTra : dim.getInstrumentTransactions()) {
					System.out.println("UPDATING :: Deal:" + dim.getDeal().getDealCode() + " Ins:" + dim.getInstrument().getInstrumentCode());
					session.saveOrUpdate(insTra);
				}
			}
			tx.commit();
			System.out.println("DIM List updated");
			insIssueReceiveDto.setSuccess(true);
		} catch (Exception ex) {
			tx.rollback();
			System.out.println("Exception in Updating the Deal. ID: " + " : " + ex.getMessage());
			ex.printStackTrace();
			insIssueReceiveDto.setSuccess(false);
			insIssueReceiveDto.setStatus(ex.getMessage());
		} finally {
			session.close();
		}

	}

	public void updateSM(Deal oldDeal, Deal newDeal, ShortMaturityDto shortMaturityDto) {
		System.out.println("Updating the SM");
		Session session = HibernateUtil.getSession().openSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.saveOrUpdate(oldDeal);
			for (DealInstrumentMap dim : oldDeal.getDealInstrumentMaps()) {
				session.saveOrUpdate(dim);
				session.saveOrUpdate(dim.getInstrument());
				for (InstrumentTransaction insTra : dim.getInstrumentTransactions()) {
					System.out.println("UPDATING :: Deal:" + dim.getDeal().getDealCode() + " Ins:" + dim.getInstrument().getInstrumentCode());
					session.saveOrUpdate(insTra);
				}
			}
			System.out.println("oldDeal updated");
			session.saveOrUpdate(newDeal);
			for (DealInstrumentMap dim : newDeal.getDealInstrumentMaps()) {
				session.saveOrUpdate(dim);
				session.saveOrUpdate(dim.getInstrument());
				for (InstrumentTransaction insTra : dim.getInstrumentTransactions()) {
					System.out.println("UPDATING :: Deal:" + dim.getDeal().getDealCode() + " Ins:" + dim.getInstrument().getInstrumentCode());
					session.saveOrUpdate(insTra);
				}
			}
			tx.commit();
			System.out.println("newDeal updated");
			shortMaturityDto.setSuccess(true);
		} catch (Exception ex) {
			tx.rollback();
			System.out.println("Exception in Updating the Deal. ID: " + " : " + ex.getMessage());
			ex.printStackTrace();
			shortMaturityDto.setSuccess(false);
			shortMaturityDto.setStatus(ex.getMessage());
		} finally {
			session.close();
		}

	}

	public SaledealMapping getSaleMappingByDealId(int saledealId) {
		Session session = HibernateUtil.getSession().openSession();
		SaledealMapping mapping = null;
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			mapping = (SaledealMapping) session.createQuery("from SaledealMapping S where S.saleDeal.id=:saleDealId").setParameter("saleDealId", saledealId).uniqueResult();
			tx.commit();
		} catch (Exception e) {
			if (tx != null)
				tx.rollback();
			System.out.println("Exception getByID Deal " + e.getMessage());
			e.printStackTrace();
		} finally {
			session.close();
		}
		return mapping;
	}

}
